package com.wzw.ad;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.wzw.tcjh.PageDivision;
import com.wzw.tcjh.SQLProcessor;

public class ADTools {

	public static List<Adposition> getADList(String searchCondition, PageDivision pd) {
		String sql = null;
		SQLProcessor sp = null;
		List<Adposition> adList = new ArrayList<Adposition>();
		try{
			sp = new SQLProcessor();
			sql = "select ad.ad_id, ad.m_id as m_id, ad.ad_name, media.m_name,"
				 + " ad.ad_url, ad.ad_hitcount, ad.ad_registercount, ad.projectcode"
				 + " from adposition ad"
				 + " left join media on ad.m_id=media.m_id"
				 + " where 1=1 "
				 + (searchCondition!=null?(" " + searchCondition) : "");
			List<Map<String, Object>> list = pd!=null?sp.executeQuery(sql, pd.getBegin(), pd.getPageSize()):sp.executeQuery(sql);
			for(int i=0; i<list.size(); i++){
				Adposition adPosition = new Adposition();
				Map<String, Object> adMap = list.get(i);
				adPosition.setADId(String.valueOf((Integer)adMap.get("ad_id")));
				adPosition.setMediaId(String.valueOf((Integer)adMap.get("m_id")));
				adPosition.setADName((String)adMap.get("ad_name"));
				adPosition.setMediaName((String)adMap.get("m_name"));
				adPosition.setAdUrl(((String)adMap.get("ad_url")));
				adPosition.setHitCount(String.valueOf((Number)adMap.get("ad_hitcount")));
				adPosition.setRegisterCount(String.valueOf((Number)adMap.get("ad_registercount")));
				adList.add(adPosition);
			}
			System.out.println("SQL: " + sql);
		}catch(SQLException se){
			System.out.println("Error SQL: " + sql);
			se.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			SQLProcessor.staticClose(sp);
		}
		return adList;
	}
	
	public static boolean deleteAD(String adId) throws Exception{
		SQLProcessor sp = null;
		String sql = null;
		try{
			sp = new SQLProcessor();
			sql = "delete from adposition where ad_id=" + Integer.parseInt(adId);
			System.out.println(new Date());
			System.out.println(sql);
			sp.executeUpdate(sql);
		}catch(Exception se){
			sp.rollback();
			System.out.println(sql);
			se.printStackTrace();
			throw se;
		}finally{
			SQLProcessor.staticClose(sp);
		}
		return true;
	}
	
	public static boolean addAD(Adposition ad, String projectCode) throws Exception {
		SQLProcessor sp = null;
		String sql = null;
		try {
			sp = new SQLProcessor();
			sql = "insert into adposition (m_id, ad_name, ad_url, ad_hitcount, ad_registercount, projectcode) values(" +
					"'" + ad.getMediaId() + "'" + "," +
					"'" + ad.getADName() + "'" + "," +
					"'" + ad.getAdUrl() + "'" + "," +
					"'0'," +
					"'0'," +
					"'" + projectCode + "'" +
					")";
			System.out.println(new Date());
			System.out.println(sql);
			sp.executeUpdate(sql);
		} catch (SQLException se) {
			sp.rollback();
			System.out.println(sql);
			se.printStackTrace();
			throw se;
		} finally{
			SQLProcessor.staticClose(sp);
		}
		return true;
	}
	
	public static boolean editAD(Adposition ad, String projectCode) throws Exception {
		SQLProcessor sp = null;
		String sql = null;
		try {
			sp = new SQLProcessor();
			sql = "update adposition set" +
					" ad_name='" + ad.getADName() + "'" +
//					" ad_url='" + ad.getAdUrl() + "'," +
//					" ad_hitcount=" + ad.getHitCount() + "," +
//					" ad_registercount=" + ad.getRegisterCount() +
					" where ad_id=" + ad.getADId() +
					" and projectcode='" + projectCode + "'";
			System.out.println(new Date());
			System.out.println(sql);
			sp.executeUpdate(sql);
		} catch (SQLException se) {
			sp.rollback();
			System.out.println(sql);
			se.printStackTrace();
			throw se;
		} finally{
			SQLProcessor.staticClose(sp);
		}
		return true;
	}
	
	public static boolean addMedia(Adposition ad, String projectCode) throws Exception {
		SQLProcessor sp = null;
		String sql = null;
		try {
			sp = new SQLProcessor();
			sql = "insert into media (m_name) values('" + ad.getMediaName() + "')";
			System.out.println(new Date());
			System.out.println(sql);
			sp.executeUpdate(sql);
		} catch (SQLException se) {
			sp.rollback();
			System.out.println(sql);
			se.printStackTrace();
			throw se;
		} finally{
			SQLProcessor.staticClose(sp);
		}
		return true;
	}
	
	public static List<Adposition> getMediaList(String searchCondition) {
		String sql = null;
		SQLProcessor sp = null;
		List<Adposition> adList = new ArrayList<Adposition>();
		try{
			sp = new SQLProcessor();
			sql = "select m_id, m_name"
				 + " from media"
				 + " where 1=1 "
				 + (searchCondition!=null?(" " + searchCondition) : "");
			List<Map<String, Object>> list = sp.executeQuery(sql);
			for(int i=0; i<list.size(); i++){
				Adposition adPosition = new Adposition();
				Map<String, Object> adMap = list.get(i);
				adPosition.setMediaId(String.valueOf((Integer)adMap.get("m_id")));
				adPosition.setMediaName((String)adMap.get("m_name"));
				adList.add(adPosition);
			}
			System.out.println("SQL: " + sql);
		}catch(SQLException se){
			System.out.println("Error SQL: " + sql);
			se.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			SQLProcessor.staticClose(sp);
		}
		return adList;
	}
	
	public static Adposition getADInfo(String ADId, String searchCondition) {
		String sql = null;
		SQLProcessor sp = null;
		List<Adposition> adList = new ArrayList<Adposition>();
		Adposition adPosition = null;
		try{
			sp = new SQLProcessor();
			sql = "select ad.ad_id, ad.m_id as m_id, ad.ad_name, media.m_name, ad.ad_url, ad.ad_hitcount, ad.ad_registercount"
				 + " from adposition ad"
				 + " left join media on ad.m_id=media.m_id"
				 + " where 1=1 and ad.ad_id=" + ADId
				 + (searchCondition!=null?(" " + searchCondition) : "");
			List<Map<String, Object>> list = sp.executeQuery(sql);
			if(list.size() == 1) {
				Map<String, Object> adMap = list.get(0);
				adPosition = new Adposition();
				adPosition.setADId(String.valueOf((Integer)adMap.get("ad_id")));
				adPosition.setMediaId(String.valueOf((Integer)adMap.get("m_id")));
				adPosition.setADName((String)adMap.get("ad_name"));
				adPosition.setMediaName((String)adMap.get("m_name"));
				adPosition.setAdUrl((String)adMap.get("ad_url"));
				adPosition.setHitCount(String.valueOf((Number)adMap.get("ad_hitcount")));
				adPosition.setRegisterCount(String.valueOf((Number)adMap.get("ad_registercount")));
			}
			
			System.out.println("SQL: " + sql);
		}catch(SQLException se){
			System.out.println("Error SQL: " + sql);
			se.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			SQLProcessor.staticClose(sp);
		}
		return adPosition;
	}
}
